package cc.messcat.util;

import cc.messcat.entity.system.ApplicationProperties;
import org.apache.commons.io.FileUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author handsomeWU
 * 
 */
public class Install {
	public static void dbXml(String fileName, String dbHost, String dbPort, String dbName, String dbUser, String dbPassword)
		throws Exception {
		String s = FileUtils.readFileToString(new File(fileName));
		s = s.replaceFirst("DB_HOST", dbHost);
		s = s.replaceFirst("DB_PORT", dbPort);
		s = s.replaceFirst("DB_NAME", dbName);
		s = s.replaceFirst("DB_USER", dbUser);
		s = s.replaceFirst("DB_PASSWORD", dbPassword);
		FileUtils.writeStringToFile(new File(fileName), s);
	}

	public static Connection getConn(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword)
		throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
//		String connStr = "jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName + "?user=" + dbUser + "&password=" + dbPassword;
		String connStr = "jdbc:mysql://localhost:3306/pj_caiwu?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&user=root&password=123456";
		Connection conn = DriverManager.getConnection(connStr);
		return conn;
	}
	
	public static Connection getConn()
			throws Exception {
			Class.forName("com.mysql.jdbc.Driver");
			//jdbc.username
			 String JDBC_USER_NAME = PropertiesFileReader.get("jdbc.username", "/jdbc.properties").trim();
			//jdbc.password
			 String JDBC_PASSWORD = PropertiesFileReader.get("jdbc.password", "/jdbc.properties").trim();
			 //jdbc.url
			 String JDBC_URL = PropertiesFileReader.get("jdbc.url", "/jdbc.properties").trim();
			 
			//String connStr = "jdbc:mysql://localhost:3306/povertyaid?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&user="+JDBC_USER_NAME+"&password="+JDBC_PASSWORD+"";
			String connStr = JDBC_URL+"&user="+JDBC_USER_NAME+"&password="+JDBC_PASSWORD+"";
			Connection conn = DriverManager.getConnection(connStr);
			return conn;
		}

	

	public static void webXml(String fromFile, String toFile) throws Exception {
		FileUtils.copyFile(new File(fromFile), new File(toFile));
	}

	/**
	 * 
	 * @param dbHost
	 * @param dbName
	 * @param dbPort
	 * @param dbUser
	 * @param dbPassword
	 * @throws Exception
	 */
	public static void createDb(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		String connStr = "jdbc:mysql://" + dbHost + ":" + dbPort + "?user=" + dbUser + "&password=" + dbPassword;
		Connection conn = DriverManager.getConnection(connStr);
		Statement stat = conn.createStatement();
		String sql = "drop database if exists " + dbName;
		stat.execute(sql);
		sql = "create database " + dbName + " ";
		stat.execute(sql);
		stat.close();
		conn.close();
	}

	public static void changeDbCharset(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword)
		throws Exception {
		Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword);
		Statement stat = conn.createStatement();
		String sql = "alter database " + dbName + " character SET UTF8";
		stat.execute(sql);
		stat.close();
		conn.close();
	}

	/**
	 * ������
	 * 
	 * @param dbHost
	 * @param dbName
	 * @param dbPort
	 * @param dbUser
	 * @param dbPassword
	 * @param sqlList
	 * @throws Exception
	 */
	public static void createTable(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword,
		List<String> sqlList) throws Exception {
		sqlList = new ArrayList();
		String a= null;
		for (int i = 0; i < 10000; i++) {
			 a = "insert into enterprise(company_name,state) values('p"+i+"','s"+i+"')";
			 sqlList.add(a);
		}
		
		
		Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword);
		Statement stat = conn.createStatement();
		for (String dllsql : sqlList) {
			stat.addBatch(dllsql);
		}

		stat.executeBatch();
		stat.close();
		conn.close();
	}

	/**
	 * ��������
	 * 
	 * @param dbHost
	 * @param dbName
	 * @param dbPort
	 * @param dbUser
	 * @param dbPassword
	 * @param domain
	 * @param cxtPath
	 * @param port
	 * @throws Exception
	 */
	public static void updateConfig(String dbHost, String dbPort, String dbName, String dbUser, String dbPassword, String domain,
		String cxtPath, String port) throws Exception {
		Connection conn = getConn(dbHost, dbPort, dbName, dbUser, dbPassword);
		Statement stat = conn.createStatement();

		String sql = "";
		// 如果不为默认端口,填对主页添加端口
		if (!"80".equals(port))
			sql = "update WEB_SITE set DOMAIN='" + domain + ":" + port + "'";
		else
			sql = "update WEB_SITE set DOMAIN='" + domain + "'";
		stat.executeUpdate(sql);

		stat.close();
		conn.close();
	}

	/**
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static List<String> readSql(String fileName) throws Exception {
		BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), "utf-8"));
		List<String> sqlList = new ArrayList<String>();
		StringBuilder sqlSb = new StringBuilder();
		String s = null;
		while ((s = br.readLine()) != null) {
			if (s.startsWith("/*")) {
				continue;
			}
			if (s.endsWith(";")) {
				sqlSb.append(s);
				sqlSb.setLength(sqlSb.length());
				sqlList.add(sqlSb.toString());
				sqlSb.setLength(0);
			} else {
				sqlSb.append(s);
			}
		}
		br.close();
		return sqlList;
	}

	/**
	 * jdbc批处理
	 * 
	 * @param sql
	 *            sql语句
	 * @param dataCount
	 *            需要的参数个数
	 * @param dataList
	 *            需要的参数集合
	 * @throws SQLException
	 * 
	 */
	@SuppressWarnings("unchecked")
	public static void executeBatch(String sql, Integer dataCount, List<Map<String, Object>> dataList)
			throws SQLException {
		long str = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 获取连接
			conn = Install.getConn();
			// 关闭自动提交
			conn.setAutoCommit(false);
			// 创建对象
			ps = conn.prepareStatement(sql);
			// 循环的次数
			Map<String, Object> map0 = null;
			for (int i = 0; i < dataList.size(); i++) {
				map0 = (Map<String, Object>) dataList.get(i);
				//判断是否贫困户导入，替换数据
				if(dataCount.equals(30) || dataCount.equals(31)) {
                    map0.put("17", ApplicationProperties.getPoorFamilyCount(ApplicationProperties.getAnnual(),(Long)map0.get("4")));
                    map0.put("18", ApplicationProperties.getFamilyCount(ApplicationProperties.getAnnual(),(Long)map0.get("4")));
                }
                // 循环参数
                for (int j = 0; j < dataCount; j++) {
					ps.setObject(j + 1, map0.get(String.valueOf(j + 1)));
				}
				// 累加到批处理
				ps.addBatch();
				if ((i + 1) % 1000 == 0) {
					// 执行批处理
					ps.executeBatch();
					conn.commit();
					ps.clearBatch();
				}
			}
			// 最后再执行批处理
			ps.executeBatch();
			conn.commit();
			ps.clearBatch();
		} catch (Exception e) {
			conn.rollback(); // 回滚事务
			e.printStackTrace();
		} finally {
			// 关闭资源
			conn.close();
			ps.close();
		}
		long end = System.currentTimeMillis();
		System.out.println(end - str);

	}
	
	
	
}
